package com.vp.org.constant;

import com.vp.org.pojo.IntegralExchange;
import com.vp.org.pojo.UserInfo;

public interface Sql {

    // 用户登录验证
    String USERINFO_LOGINVERIFY = "select phone,password from user_info  where phone = ? and password = ?";

    // 添加用户(注册用户)
    String USERINFO_ADDUSER = "insert into user_info(nickname,password,phone,qq,username,address,city,district,classname,grade,family_phone,postcode,province,type,school,integral,picture,email,hobby,introduce,birthday,gender) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    // 用户信息修改
    String USERINFO_UPDATEUSERINFO = "update  user_info set nickname = ?,password = ?,phone = ? ,qq = ?,username = ?,address= ?,city= ?,district= ?,classname = ?,grade= ?,family_phone= ?,postcode= ?,province= ?,type =?,school=?,integral = ?,picture = ?,email = ?,hobby = ?,introduce = ?,birthday = ?,gender = ? where user_id = ?";

    // 用户共享数据
    String USERINFO_SHAREDATA = "select * from user_info where phone= ? and password =?";

    //用作注册验证
    String USERINFO_REGISTERVERIFY = "select * from user_info where phone = ?";

    // 管理员查询所有用户
    String USERINFO_SELECTUSERINFO = "select * from user_info";


    // 查询所有书籍
    String BOOKINFO_QUERYALLBOOKS ="select * from book_info order by upload_time desc limit 0,5";

    //书籍详情
    String BOOKINFO_QUERYBOOKDETAILS ="select * from book_info where id = ?";

    //模糊查询书籍
    String BOOKINFO_FUZZYQUERYBOOK = "select * from book_info where name like ? limit ?,9";

    // 模糊查询书籍页数
    String BOOKINFO_FUZZYQUERYBOOKPAGE = "select count(*) count from book_info where name like ?";

    //查询书籍分页查询
    String BOOKINFO_BOOKPAGINGQUERY = "select * from book_info where grade = ? limit ?,?";

    //查询书籍分页页数
    String BOOKINFO_PAGINGSHOWPAGE="select count(*) count from book_info where  grade = ?";

    //后台添加书籍
    String BOOKINFO_ADMINADDBOOK="insert into book_info values(default,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    // 后台查询书籍
    String BOOKINFO_ADMINQUERYBOOK="select id,product_num,name,price,discount_price,img,discount,status,type,fit_grade,author,publish_house,publish_date,integral,upload_time from book_info where admin = ? limit ?,6";

    //后台查询书籍页数
    String BOOKINFO_ADMINQUERYBOOKPAGE="select count('id') count from book_info";

    //后台书籍分页查询
    String BOOKINFO_ADMINBOOKPAGINGQUERY = "select id,product_num,name,price,discount_price,img,discount,status,type,fit_grade,author,publish_house,publish_date,integral,upload_time from book_info limit ?,?";

    // 后台书籍删除
    String BOOKINFO_ADMINDELETEBOOK = "delete from book_info where id = ?";

    // 后台书籍修改
    String BOOKINFO_ADMINUPADTEBOOK = "update book_info set product_num = ?,name=?,price=?,discount_price=?,img=?,discount=?,status=?,type=?,fit_grade=?,author=?,publish_house=?,publish_date=?,integral=?,upload_time=? where id = ?";

    //促销商品总数
    // select count(discount) from book_info where discount < 1;
    String BOOKINFO_PROMOTIONCOUNT = "select count(discount) count from book_info where discount < 1";

    // 促销商品分页查询
    // select id,product_num,name,price,discount_price,img,discount,status,type,fit_grade,author,publish_house,publish_date,integral,upload_time from book_info where discount < 1 limit 0,6;
    String BOOKINFO_PROMOTIONBOOKS = "select id,product_num,name,price,discount_price,img,discount,status,type,fit_grade,author,publish_house,publish_date,integral,upload_time from book_info where discount < 1 limit ?,6";



    // 后台管理员登录
    String ADMININFO_ADMINLOGIN = "select * from admin_info where admin = ? and  password = ?";

    // 删除供货商
    // delete from admin_info where admin = ?;
    String ADMININFO_DELETEADMIN = "delete from admin_info where admin = ?";

    // 修改供货商
    // update admin_info set id = ?,password = ?,department = ?,name = ?,organization = ?,address = ?,phone = ?,email = ?,qq = ? where admin = ?;
    String ADMININFO_UPDATEADMIN = "update admin_info set id = ?,password = ?,department = ?,name = ?,organization = ?,address = ?,phone = ?,email = ?,qq = ? where admin = ?";

    // 货商总数
    // select count(id) from admin_info;
    String ADMININFO_COUNTADMIN = "select count(id) count from admin_info";

    // 分页查询供货商
    // select * from admin_info where admin != 'gsh'order by id limit 0,3;
    String ADMININFO_QUERYADMIN = "select * from admin_info where admin != 'gsh'order by id limit ?,3";



    //添加评论
    String REMARK_ADD = "insert into remark values (default,?,?,?,default,default,?)";

    //删除评论
    String REMARK_DELETE = "delete from remark where id = ? and user_id = ?";
    String DELETE = "DELETE remark WHERE ID = ?";

    // 总评论数
    String REMARK_COUNT ="select count('id') count from remark;";

    // 后台分页查询
    // String REMARK_QUERY= "select id,r.user_id,book_id,book_name,content,nickname,password,phone,qq,username,province,city,district,address,type,school,grade,classname,postcode,picture,email from remark r,user_info u  WHERE r.user_id = u.user_id  ORDER BY id desc LIMIT ?,6";
    String REMARK_QUERY = "select u.username,u.phone,r.* from user_info u,remark r where u.user_id = r.user_id limit ?, 6";

    // 查询所有评论
    String ALL_REMARK = "SELECT * FROM REMARK WHERE USER_ID = ?";


    // 后台订单总数
    // select count('id') count from orders
    String ORDERS_ORDERSPAGECOUNT = "select count('id') count from orders";

    // 后台订单分页查询
    // select o.*,u.address from user_info u,orders o,book_info b where  b.admin='admin' and u.user_id=o.user_id limit 0,4;
    String ORDERS_ORDERSPAGEQUERY = "select o.*,u.address from user_info u,orders o,book_info b where  b.admin=? and u.user_id=o.user_id limit ?,4";

    // 后台删除订单
    // delete from orders where id = ?;
    String ORDERS_DELETE = "delete from orders where id = ?";

    // 销售额（商品总价格）
    // select sum(price * count) from orders;
    String ORDERS_SALE = "select sum(price * count) sum from orders";


    // 删除兑换记录
    // delete from record where id = ?;
    String RECORD_DELETERECORD = "delete from record where id = ?";

    // 记录总数
    // select count(id) count from record;
    String RECORD_COUNT = "select count(id) count from record";

    // 分页查询兑换记录
    // select * from record limit 0,4;
    String RECORD_QUERYPAGE = "select * from record limit ?,4";


    // 上架可兑换商品（增加）
    // insert into integral_exchange values (default,?,?,?,?);
    String INTEGRAL_ADD = "insert into integral_exchange values (default,?,?,?,?)";

    // 下架可兑换商品（删除）
    // delete from integral_exchange where id = ?;
   String INTEGRAL_DELETE = "delete from integral_exchange where id = ?";

    // 修改可兑换商品
    // update integral_exchange set integral = ?,  admin    = ?,goods_name = ?,goods_type = ?where id = ?;
  String INTEGRAL_UPDATE = "update integral_exchange set integral = ?,admin = ?,goods_name = ?,goods_type = ?where id = ?";

    // 查询所有可兑换商品总数
    // select count(id) count from integral_exchange;
   String INTEGRAL_COUNT = "select count(id) count from integral_exchange";

    // 分页查询可兑换商品
    // select * from integral_exchange limit 0,4;
    String INTEGRAL_QUERYPAGE = "select * from integral_exchange limit ?,4";


    // 后台注册用户
    // insert into user_info (phone,password,email,family_phone,qq) values (?,?,?,?,?);
    String USER_ADD = "insert into user_info (phone,password,email,family_phone,qq,integral) values (?,?,?,?,?,?)";

    // 后台删除用户
    // delete from user_info where phone = ?;
   String USER_DELETE = "delete from user_info where phone = ?";

    // 后台总数
    // select count(user_id) count from user_info;
    String USER_COUNT = "select count(user_id) count from user_info";

    // 管理员分页查询所有用户
    // select * from user_info limit 0,3;
    String USER_PAGE = "select * from user_info limit ?,3";

}
